import 'package:flutter/material.dart';
import 'package:sqlite3/sqlite3.dart';

import 'package:path/path.dart' as path;
import 'package:path_provider/path_provider.dart';

class ToolsDatabase {
  static String db_url = "db.sqlite";

  // 连接数据库
  // static Future<Database> openDatabase() async {
  //   final docsDir = await getApplicationDocumentsDirectory();
  //   db_url = path.join(docsDir.path, 'db.sqlite');
  //
  //   Database db = sqlite3.open(db_url);
  //   print(db);
  //   return db;
  // }

  static bool _inited = false;

  static Future<Database> openDatabase() async {
    final docsDir = await getApplicationDocumentsDirectory();
    db_url = path.join(docsDir.path, 'db.sqlite');
    print("数据库路径:db_url:${db_url}");
    Database db = sqlite3.open(db_url);
    print(db);
    init(db);
    return db;
  }

  static init(db) {
    // 初始化数据库
    if(_inited)
      {
        return;
      }
    _inited = true;

    String sql = '''
    CREATE TABLE if not exists "p001_user_memory_curve" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "user_id" INTEGER,
  "review_progress" Integer,
  "review_time" integer,
  "gmt_create" datetime,
  "gmt_modified" datetime,
  "remark" TEXT
);
    ''';

    final stmt = db.prepare(sql);
    stmt..execute();

    String sql2 = '''
    INSERT INTO 
      "p001_user_memory_curve" ("id", "user_id", "review_progress", "review_time", "gmt_create", "gmt_modified", "remark") 
    VALUES 
    (1, NULL, 1, 30000, NULL, NULL, NULL),
    (2, NULL, 2, 1800000, NULL, NULL, NULL),
    (3, NULL, 3, 7200000, NULL, NULL, NULL)
,(4, NULL, 4, 86400000, NULL, NULL, NULL)
,(5, NULL, 5, 172800000, NULL, NULL, NULL)
,(6, NULL, 6, 345600000, NULL, NULL, NULL)
,(7, NULL, 7, 604800000, NULL, NULL, NULL)
,(8, NULL, 8, 1296000000, NULL, NULL, NULL)
,(9, NULL, 9, 2592000000, NULL, NULL, NULL)
,(10, NULL, 10, 7776000000, NULL, NULL, NULL)
,(11, NULL, 11, 15552000000, NULL, NULL, NULL)
,(12, NULL, 12, 31536000000, NULL, NULL, NULL);
    ''';

    final stmt2 = db.prepare(sql);
    stmt2..execute();

    sql = '''
    CREATE TABLE if not exists "p001_user_album" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" TEXT(255),
  "file_path" TEXT(255),
  "type" TEXT(255),
  "language_id" integer,
  "media_qty" integer,
  "finish_qty" integer DEFAULT 0,
  "word_qty" integer,
  "sentence_qty" integer,
  "speed" integer,
  "level" integer,
  "last_study_time" integer,
  "gmt_create" integer,
  "gmt_modified" integer,
  "remark" TEXT(255)
);
''';

    final stmt3 = db.prepare(sql);
    stmt3..execute();

    sql = '''
    CREATE TABLE if not exists "p001_user_album_media" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "album_id" INTEGER,
  "file_name" TEXT(255),
  "file_path" TEXT,
  "sentence_qty" INTEGER,
  "finish_qty" INTEGER DEFAULT 0,
  "word_qty" INTEGER,
  "speed" INTEGER,
  "level" INTEGER,
  "last_study_time" INTEGER,
  "gmt_create" INTEGER,
  "gmt_modified" INTEGER,
  "remark" TEXT(255)
);
''';
    final stmt4 = db.prepare(sql);
    stmt4..execute();

    sql = '''
CREATE TABLE if not exists "p001_user_album_media_record" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "user_id" INTEGER,
  "album_id" INTEGER,
  "media_id" INTEGER,
  "sentence_id" INTEGER,
  "sentence" TEXT(255),
  "start_time" INTEGER,
  "end_time" INTEGER,
  "status" INTEGER,
  "start_review_time" INTEGER,
  "review_progress" INTEGER DEFAULT 0,
  "last_review_time" Integer,
  "deferred" integer,
  "right_qty" INTEGER DEFAULT 0,
  "wrong_qty" INTEGER DEFAULT 0,
  "gmt_create" INTEGER,
  "gmt_modified" INTEGER,
  "remark" TEXT(255)
);
);
''';
    final stmt5 = db.prepare(sql);
    stmt5..execute();

    sql = """
    CREATE TABLE if not exists "p001_user_word" (
  "id" INTEGER NOT NULL,
  "user_id" INTEGER,
  "language_id" INTEGER,
  "word" TEXT(255),
  "type" integer,
  "start_review_time" integer,
  "last_review_time" integer,
  "review_progress" integer,
  "deferred" integer,
  "say_right_qty" integer,
  "input_right_qty" integer,
  "write_right_qty" integer,
  "say_wrong_qty" integer,
  "input_wrong_qty" integer,
  "write_wrong_qty" integer,
  "listen_qty" integer,
  "read_qty" integer,
  "gmt_create" INTEGER,
  "gmt_modified" INTEGER,
  "remark" TEXT(255),
  PRIMARY KEY ("id")
);
    """;

    final stmt6 = db.prepare(sql);
    stmt6..execute();


    sql = """
    CREATE TABLE if not exists "p001_user_memory_curve" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "user_id" INTEGER,
  "review_progress" Integer,
  "review_time" integer,
  "gmt_create" datetime,
  "gmt_modified" datetime,
  "remark" TEXT
);
""";
    final stmt7 = db.prepare(sql);
    stmt7..execute();

    sql = "select * from p001_user_memory_curve";
    ResultSet rs = db.select(sql);
    if(rs.isEmpty)
    {
      sql = """
      INSERT INTO "p001_user_memory_curve" ("id", "user_id", "review_progress", "review_time", "gmt_create", "gmt_modified", "remark") VALUES 
      (1, NULL, 1, 30000, NULL, NULL, NULL)
      ,(2, NULL, 2, 1800000, NULL, NULL, NULL)
      ,(3, NULL, 3, 7200000, NULL, NULL, NULL)
      ,(4, NULL, 4, 86400000, NULL, NULL, NULL)
      ,(5, NULL, 5, 172800000, NULL, NULL, NULL)
      ,(6, NULL, 6, 345600000, NULL, NULL, NULL)
      ,(7, NULL, 7, 604800000, NULL, NULL, NULL)
      ,(8, NULL, 8, 1296000000, NULL, NULL, NULL)
      ,(9, NULL, 9, 2592000000, NULL, NULL, NULL)
      ,(10, NULL, 10, 7776000000, NULL, NULL, NULL)
      ,(11, NULL, 11, 15552000000, NULL, NULL, NULL)
      ,(12, NULL, 12, 31536000000, NULL, NULL, NULL);
""";
      final stmt8 = db.prepare(sql);
      stmt8..execute();
    }

  }
}
